package com.manage.utils;

import com.baomidou.mybatisplus.toolkit.IdWorker;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

import java.io.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author Fitch
 */
public class ReadExcelOrText {

    public static void main(String[] args) {

        String url = "C:\\Users\\Fitch\\Desktop\\APP\\1014.txt";
        readFileByLines1(url);
        //readExcel1();
//        readFileByChar(url);
     //   A1301_2();
        //A031303_4();

    }
    public static void readFileByChar(String fileName) {
        File file = new File(fileName);
        BufferedReader reader = null;
        try {
            System.out.println("以行为单位读取文件内容，一次读一整行：");
            reader = new BufferedReader(new FileReader(file));
            String tempString = null;
            int line = 1;
            // 一次读入一行，直到读入null为文件结束
            String temp1="",temp2="";
            int id1 = 15162881;
            int id2 = 19612;
            while ((tempString = reader.readLine()) != null) {
                String[] strArr;
                for(int i= 0;i<tempString.length();i++) {
                    String s = tempString.substring(i, i + 1);

                    temp1 = "INSERT INTO `k_material_detail` (`id`, `content`, `status`, `create_time`, `create_user`, `update_time`, `update_user`, `remarks`) VALUES ('" + id1 + "', '" + s + "', '0', now(), '0', now(), '0', NULL);";

                    temp2 = "INSERT INTO `k_subject_material` (`id`, `material_detail_id`, `project_type`, `project_code`, `start_age`, `end_age`, `start_grade`, `end_grade`, `level`, `status`, `create_user`, `create_time`, `update_user`, `update_time`, `remarks`) VALUES ('" + id2 + "', '" + id1 + "', '13', '1305', '4', '14', '0', '9', NULL, '0', '0', now(), '0', now(), NULL);";

                    System.out.println(temp1);
                    System.out.println(temp2);
                    line++;
                    id1++;
                    id2++;
                }
            }
            reader.close();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (reader != null) {
                try {
                    reader.close();
                } catch (IOException e1) {
                }
            }
        }
    }
    public static void readFileByLines1(String fileName) {
        File file = new File(fileName);
        BufferedReader reader = null;
        try {
            System.out.println("以行为单位读取文件内容，一次读一整行：");
            reader = new BufferedReader(new FileReader(file));
            String tempString = null;
            // 一次读入一行，直到读入null为文件结束
            String temp1="",temp2="";
            while ((tempString = reader.readLine()) != null) {
                if(StrUtil.isEmpty(tempString)){
                    continue;
                }
                Long id1 = IdWorker.getId();
                if(tempString.contains("AND `id` =")){

                    System.out.println(tempString);
                }
                //temp1 = "INSERT INTO `ies_oies_test`.`k_material_detail`(`id`, `project_type_code`, `project_code`, `project_difficulty`, `content`, `type`, `config`, `status`, `remarks`, `create_time`, `create_user`, `update_time`, `update_user`) VALUES ("+id1+", 'A13', 'A1305', '1-6', '"+tempString.trim()+"', 0, NULL, 0, '', now(), '0', now(), '0');\n";

                //temp2 = "INSERT INTO `k_subject_material` (`id`, `material_detail_id`, `project_type`, `project_code`, `start_age`, `end_age`, `start_grade`, `end_grade`, `level`, `status`, `create_user`, `create_time`, `update_user`, `update_time`, `remarks`) VALUES ('"+id2+"', '"+id1+"', '15', '1513', '6', '7', '1', '2', NULL, '0', '0', now(), '0', now(), NULL);";
                //System.out.println(temp1);
                //System.out.println(temp2);
            }
            reader.close();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (reader != null) {
                try {
                    reader.close();
                } catch (IOException e1) {
                }
            }
        }
    }

    /**
     * oies固定项目
     */
    private static void readExcel1(){
        ReadExcelOrText obj = new ReadExcelOrText();
        // 此处为我创建Excel路径：E:/zhanhj/studysrc/jxl下
        File file = new File("C:\\picture\\7-8.xls");
        List excelList = obj.readExcel(file);
        int classTimes=1;
        int id=101;
        System.out.println("list中的数据打印出来");
        for (int i = 0; i < 100; i++) {
            List list0 = (List) excelList.get(0);
            List list2 = (List) excelList.get(2);
            List list3 = (List) excelList.get(3);
            List list4 = (List) excelList.get(4);
            List list5 = (List) excelList.get(5);
            String code = getCode(list2.get(i))+","+getCode(list3.get(i))+","+getCode(list4.get(i))+","+getCode(list5.get(i));
            String name =list2.get(i)+","+list3.get(i)+","+ list4.get(i)+","+list5.get(i);
            String sql="INSERT INTO `ies_oies_dev`.`k_class_subject` (`id`, `project_parent_code`,`project_parent_name`, `start_age`, `end_age`, `class_times`, `status`, `create_time`, `create_user`, `update_time`, `update_user`, `remarks`) VALUES ('"+id+"', '"+code+"','"+name+"', '7', '8', '"+classTimes+"', '0', now(), '0', now(), '0', NULL);";
            System.out.println(sql);
            id++;
            classTimes++;

        }
    }

    private static String getCode(Object projectName){
        String code ="";
        switch (projectName.toString()){
            case "视动":code = "02";break;
            case "音频游戏":code = "16";break;
            case "反应游戏":code = "16";break;
            case "听记忆":code = "13";break;
            case "听注意":code = "12";break;
            case "视注意":code = "01";break;
            case "理解/思维":code = "04";break;
            case "视记忆":code = "03";break;
        }
        return code;
    }

    private static void A031303_4(){
        ReadExcelOrText obj = new ReadExcelOrText();
        // 此处为我创建Excel路径：E:/zhanhj/studysrc/jxl下
        File file = new File("C:\\Users\\Fitch\\Desktop\\题目_2.0\\A03视记忆\\记忆数学公式0830.xls");
        List excelList = obj.readExcel(file);
        excelList.remove(0);
        System.out.println(excelList.size());
        Integer dif =15;
        Integer temp =15001;
        for(int i=0;i<excelList.size();i++){
            String subjectNo = "A031303";

            subjectNo = subjectNo + StrUtil.getString(temp);
            Long id = IdWorker.getId();
            List list1 = (List) excelList.get(i);
            String optionA = StrUtil.getString(list1.get(1));
            String result = optionA;
            String optionB = StrUtil.getString(list1.get(2));
            List list2 = (List) excelList.get(i+1);
            String optionC = StrUtil.getString(list2.get(0));
            List list3 = (List) excelList.get(i+2);
            String optionD = StrUtil.getString(list3.get(0));
            String content="[{&quot;type&quot;:&quot;text&quot;,&quot;attribute&quot;:&quot;subjectContent&quot;,&quot;value&quot;:&quot;"+result+"&quot;,&quot;conversion&quot;:true}]";
            String option = "[{&quot;type&quot;:&quot;text&quot;,&quot;problemAttribute&quot;:&quot;problemText&quot;,&quot;value&quot;:&quot;请选择？&quot;,&quot;conversion&quot;:true},{&quot;type&quot;:&quot;text&quot;,&quot;problemAttribute&quot;:&quot;optionA&quot;,&quot;value&quot;:&quot;"+optionA+"&quot;,&quot;conversion&quot;:true},{&quot;type&quot;:&quot;text&quot;,&quot;problemAttribute&quot;:&quot;optionB&quot;,&quot;value&quot;:&quot;"+optionB+"&quot;,&quot;conversion&quot;:true},{&quot;type&quot;:&quot;text&quot;,&quot;problemAttribute&quot;:&quot;optionC&quot;,&quot;value&quot;:&quot;"+optionC+"&quot;,&quot;conversion&quot;:true},{&quot;type&quot;:&quot;text&quot;,&quot;problemAttribute&quot;:&quot;optionD&quot;,&quot;value&quot;:&quot;"+optionD+"&quot;,&quot;conversion&quot;:true},{&quot;type&quot;:&quot;text&quot;,&quot;problemAttribute&quot;:&quot;result&quot;,&quot;value&quot;:&quot;"+result+"&quot;},{&quot;type&quot;:&quot;text&quot;,&quot;problemAttribute&quot;:&quot;type&quot;,&quot;value&quot;:&quot;radio&quot;,&quot;data&quot;:&quot;radio&quot;}]";
            String sql = "INSERT INTO `ies_oies_test`.`k_integration_subject`(`id`, `subject_id`, `content`, `options`, `status`, `difficulty`, `code`, `instruction`, `point_config`, `update_time`, `create_time`, `type`) VALUES ("+id+", '"+subjectNo+"', '"+content+"', '"+option+"', 0, '"+dif+"', 'A031303', NULL, NULL, NULL, now(), NULL);\n";
            System.out.println(sql);
            temp++;
            i+=2;
        }

    }

    private static void A1301_2(){
        ReadExcelOrText obj = new ReadExcelOrText();
        // 此处为我创建Excel路径：E:/zhanhj/studysrc/jxl下
        File file = new File("C:\\Users\\Fitch\\Desktop\\题目_2.0\\A13听记忆\\听词复述难度12-15.xls");
        List excelList = obj.readExcel(file);

        int subjectNo =130115000;
        int dif = 15;
        for(int i =1;i<excelList.size();i++){
            Long id = IdWorker.getId();
            String subjectNoStr = "A"+(subjectNo+i);
            List tempList =(List) excelList.get(i);
            if(tempList.size() == 0){
               continue;
            }
            String content = (String) tempList.get(1);
            String option = (String) tempList.get(2);
            String optionA = option.split("\n")[0].replace("A.","");
            optionA =optionA.substring(0,optionA.length()-1);
            String optionB = option.split("\n")[1].replace("B.","");
            optionB =optionB.substring(0,optionB.length()-1);
            String sql ="INSERT INTO `ies_oies_test`.`k_integration_subject`" +
                    "(`id`, `subject_id`, `content`, `options`, `status`, `difficulty`, `code`, `instruction`, `point_config`, `update_time`, `create_time`, `type`) " +
                    "VALUES ("+id+", '"+subjectNoStr+"', '[{&quot;type&quot;:&quot;audio&quot;,&quot;attribute&quot;:&quot;subjectContent&quot;,&quot;value&quot;:&quot;1151431246102925313&quot;,&quot;conversion&quot;:true}]', '[{&quot;type&quot;:&quot;text&quot;,&quot;problemAttribute&quot;:&quot;problemText&quot;,&quot;value&quot;:&quot;你听到的是？&quot;,&quot;conversion&quot;:true},{&quot;type&quot;:&quot;text&quot;,&quot;problemAttribute&quot;:&quot;optionA&quot;,&quot;value&quot;:&quot;"+optionA+"&quot;,&quot;conversion&quot;:true},{&quot;type&quot;:&quot;text&quot;,&quot;problemAttribute&quot;:&quot;optionB&quot;,&quot;value&quot;:&quot;"+optionB+"&quot;,&quot;conversion&quot;:true},{&quot;type&quot;:&quot;text&quot;,&quot;problemAttribute&quot;:&quot;result&quot;,&quot;value&quot;:&quot;"+optionA+"&quot;},{&quot;type&quot;:&quot;text&quot;,&quot;problemAttribute&quot;:&quot;type&quot;,&quot;value&quot;:&quot;radio&quot;,&quot;data&quot;:&quot;radio&quot;,&quot;conversion&quot;:true}]', 0, '"+dif+"', 'A1301', NULL, NULL, NULL, now(), NULL);\n";
            System.out.println(sql);

        }

    }
  // 去读Excel的方法readExcel，该方法的入口参数为一个File对象
    public List readExcel(File file) {
        try {
            // 创建输入流，读取Excel
            InputStream is = new FileInputStream(file.getAbsolutePath());
            // jxl提供的Workbook类
            Workbook wb = Workbook.getWorkbook(is);
            // Excel的页签数量
            int sheet_size = wb.getNumberOfSheets();
            for (int index = 13; index < sheet_size; index++) {
                List<List> outerList=new ArrayList<List>();
                // 每个页签创建一个Sheet对象
                Sheet sheet = wb.getSheet(index);
                // sheet.getRows()返回该页的总行数
                for (int i = 0; i < sheet.getRows(); i++) {
                    List innerList=new ArrayList();
                    // sheet.getColumns()返回该页的总列数
                    for (int j = 0; j < sheet.getColumns(); j++) {
                        String cellinfo = sheet.getCell(j, i).getContents();
                        if(cellinfo.isEmpty()){
                            continue;
                        }
                        innerList.add(cellinfo);
                        //System.out.print(cellinfo);
                    }
                    outerList.add(i, innerList);
                    //System.out.println();
                }
                return outerList;
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (BiffException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }
}
